<?php

/**
 * This file is part of the Codizer package.
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @copyright   Copyright (c) 2010 Advisa (http://www.advisa.fr)
 * @author      Codizer Core Team <team@codizer.org>
 * @package     Codizer
 */

class f_database_Provider
{
	/**
	 * Singleton
	 *
	 * @var f_database_Provider
	 */
	private static $instance;

	/**
	 * @var PDO
	 */
	private $dbConnexion = null;

	/**
	 * @var PDOStatement
	 */
	private $query = null;

	/**
	 * loaded ini configuration
	 *
	 * @var string
	 */
	private $cfgDsn;
	private $cfgHost;
	private $cfgLogin;
	private $cfgPassword;
	private $cfgDbName;
	private $usePersist;

	protected $hasActiveTransaction = false;

    private $transactionInstances = 0;

	/**
	 * Constructor
	 */
	private function __construct()
	{
		$this->loadConfig();
		$this->connect();
		$this->initEncoding();
	}

	/**
	 * Get the current instance or create a new instance of f_database_Provider
	 *
	 * @return f_database_Provider
	 */
	public static function getInstance()
	{
		if(is_null(self::$instance))
		{
			self::$instance = new self();
		}
		return self::$instance;
	}

	// ================================================= CONFIGURATION

	/**
	 * Load initial config from configLoader
	 */
	private function loadConfig()
	{
		$config = f_loader_Config::getInstance()->getParameters("DBConfig");

		try
		{
			$this->cfgDsn = $config['driver'];
			$this->cfgHost = $config['host'];
			$this->cfgLogin = $config['login'];
			$this->cfgPassword = $config['password'];
			$this->cfgDbName = $config['dbName'];
			$config['usePersist'] == "1" ? $this->usePersist = true : $this->usePersist = false;		
		}
		catch(Exception $e)
		{
			throw new Exception($e);
		}
	}

    // ================================================= CONNEXION

    public function getNewInstance()
    {
        self::$instance = new self();
    }
    
    public static function checkConnect()
    {
        $config = f_loader_Config::getInstance()->getParameters("DBConfig");

        try
        {           
            $test = new PDO($config['driver'].':host='.$config['host'].';dbname='.$config['dbName'],
                $config['login'],
                $config['password']);

            $test->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $test->exec('SHOW TABLES');
        }
        catch (PDOException $e)
        {
            return false;
        }

        return true;
    }

	/**
	 * Create PDO connection to the database
	 *
	 * @param string $host
	 * @param string $dbName
	 * @param string $user
	 * @param string $password
	 */
	private function connect()
	{
		try
		{
			$this->dbConnexion = new PDO($this->cfgDsn.':host='.$this->cfgHost.';dbname='.$this->cfgDbName,
			$this->cfgLogin,
			$this->cfgPassword,
			array(PDO::ATTR_PERSISTENT => $this->usePersist));

			$this->dbConnexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		}
		catch(PDOException $e)
		{
			throw new Exception($e);
		}
	}

	private function initEncoding()
	{
		$this->dbConnexion->exec("SET NAMES 'UTF8'");
	}

	// ================================================= TRANSACTION MANAGEMENT

	/**
	 * Begin a new transaction, only for innoDb tables
	 */
	public function beginTransaction()
	{
        $this->transactionInstances++;

		if ($this->hasActiveTransaction)
		{           
			return false;
		}
		else
		{
			$this->hasActiveTransaction = $this->dbConnexion->beginTransaction();
			return $this->hasActiveTransaction;
		}
	}

	/**
	 * Commit the query result(s)
	 */
	public function commit()
	{
        $this->transactionInstances--;

        if($this->transactionInstances == 0)
        {
            $this->dbConnexion->commit();
            $this->hasActiveTransaction = false;
        }
	}

	/**
	 * Rollback the query result(s)
	 */
	public function rollBack($e = null)
	{
		$this->dbConnexion->rollBack();
		$this->hasActiveTransaction = false;
        $this->transactionInstances = 0;
        if(!is_null($e))
        {
            throw new Exception($e);
        }
	}

	// ================================================= QUERY

	/**
	 * Prepare a SQL query
	 *
	 * @param string $query
	 * @return f_database_Provider
	 */
	public function setQuery($query)
	{
		$this->query = $this->dbConnexion->prepare($query);
		return $this;
	}

	/**
	 * Bind parameter when query is set
	 *
	 * @param string $parameter
	 * @param mixed $variable
	 * @return f_database_Provider
	 */
	public function bindParam($parameter, $variable)
	{
		$this->query->bindParam($parameter, $variable);
		return $this;
	}

	/**
	 * Execute the query setted by setQuery()
	 *
	 * @var array $args optionnal arguments
	 * @return f_database_Provider
	 */
	public function execute($args = null)
	{
		if(!is_null($this->query))
		{
			$config = f_loader_Config::getInstance()->getParameters("default");
			$logger = f_logs_Logger::getInstance();
				
			try
			{
				$startTime = microtime(true);
				$result = $this->query->execute($args);
				$time = round(microtime(true) - $startTime, 5);
			}
			catch(PDOException $e)
			{
				$logger->sqlLog($this->mergeQueryParams($args));
				throw new Exception($e);
			}

			$logger->sqlLog($this->mergeQueryParams($args), $time);
				
			return $this;
		}
		throw new Exception("execute without query !");
	}

	/**
	 * For logging purpose, merge query with correct values
	 *
	 * @param array $params
	 * @return string
	 */
	private function mergeQueryParams($params)
	{
		$string = $this->query->queryString;

		if(count($params) >= 1)
		{
			// to correct str_replace, order from the longest key to the shortest one
			foreach($params as $key => $param)
			{
				$long[] = strlen($key);				
			}
			
			array_multisort($long, SORT_DESC, $params);
						
			foreach($params as $key => $param)
			{
				if($key{0} != ":")
				{
					$key = ":".$key;
				}
				
				if(is_int($param))
				{
					$string = str_replace($key, $param, $string);
				}
				else
				{
					$string = str_replace($key, "'".$param."'", $string);
				}
			}
		}

		return $string;
	}

	/**
	 * Directly execute a query (exec)
	 *
	 * @param sring $query
	 * @return integer number of affected lines
	 */
	public function executeQuery($query)
	{
		return $this->dbConnexion->exec($query);
	}

	/**
	 * Return the query result fetched in array
	 *
	 * @return array
	 */
	public function getQueryResults($fetchStyle = PDO::FETCH_BOTH, $opt = null)
	{
        if(is_null($opt))
        {
            $results = $this->query->fetchAll($fetchStyle);
        }
        else
        {
            $results = $this->query->fetchAll($fetchStyle, $opt);
        }
		$this->query->closeCursor();
		return $results;
	}

	/**
	 * Enter description here...
	 *
	 * @param string $className
	 * @return f_database_Provider
	 */
	public function setFetchIntoClass($className)
	{
		$this->query->setFetchMode(PDO::FETCH_INTO, $className);
		return $this;
	}

	/**
	 * Return the the first query result fetched in array
	 *
	 * @return array
	 */
	public function getQueryResult($fetchStyle = PDO::FETCH_BOTH)
	{
		$results = $this->query->fetch($fetchStyle);
		$this->query->closeCursor();
		if($results === false)
		{
			return null;
		}
		return $results;
	}

	// ================================================= INSERT METHOD

	/**
	 * Insert values in an table
	 * $values is an array of type 'columnName'=>value
	 *
	 * @param string $tableName
	 * @param array $values
	 * @return boolean
	 */
	public function insert($tableName, $values)
	{
		foreach($values as $key => $value)
		{
			$params[':in'.$key] = $value;
		}

		$columns = join("`,`", array_keys($values));
		$vars = join(",", array_keys($params));

		$sql = "INSERT INTO `$tableName` (`$columns`) VALUES ($vars)";

		$this->setQuery($sql)->execute($params);
	}
	
	// ================================================= DELETE METHOD
	
	/**
	 * Delete an entry in the db
	 *
	 * Example : delete("myTable", array('id' => 2));
	 *
	 * @param string $tableName
	 * @param array $values
	 * @return boolean
	 */	
	public function delete($tableName, $values)
	{
		foreach($values as $fieldName => $value)
		{
			$params[':eq'.$fieldName] = $value;
			$sqlArray[] = '`'.$fieldName.'`'.'=:eq'.$fieldName;
		}

		$conditions = join(" AND ", $sqlArray);

		$sql = "DELETE FROM `$tableName` WHERE $conditions";

		$this->setQuery($sql)->execute($params);
	}

    /**
     * Empty table
     * @param string $tableName
     */
    public function emptyTable($tableName)
    {
        $this->setQuery('TRUNCATE TABLE `'.$tableName.'`')->execute();
    }
		
	// ================================================= UPDATE METHODS

	/**
	 * Update an entry in the db for a given ID
	 *
	 * @param string $tableName
	 * @param integer $id
	 * @param array $values
	 * @return boolean
	 */
	public function update($tableName, $id, $values)
	{
		return $this->updateByKey($tableName, array("document_uid" => $id), $values);
	}

	/**
	 * Update an entry in the db
	 *
	 * @example updateByKey("myTable", array('id' => 2), array('id' => $id, 'type' => $type));
	 *
	 * @param string $tableName
	 * @param array $keyConditions
	 * @param array $values
	 * @return boolean
	 */
	public function updateByKey($tableName, $keyConditions, $values)
	{
		foreach($values as $key => $value)
		{
			$params[':fi'.$key] = $value;
			$sqlArray1[] = '`'.$key.'`'.'=:fi'.$key;
		}

		foreach($keyConditions as $fieldName => $value)
		{
			$params[':eq'.$fieldName] = $value;
			$sqlArray2[] = '`'.$fieldName.'`'.'=:eq'.$fieldName;
		}

		$fields = join(", ", $sqlArray1);
		$conditions = join(" AND ", $sqlArray2);

		$sql = "UPDATE `$tableName` SET $fields WHERE $conditions";

		$this->setQuery($sql)->execute($params);
	}

	// ================================================= RETRIEVING METHODS

    public function getDatas($tableName, $equalConditions)
    {
        foreach($equalConditions as $fieldName => $value)
		{
			$params[':eq'.$fieldName] = $value;
			$sqlArray[] = '`'.$fieldName.'`'.'=:eq'.$fieldName;
		}

        $conditions = join(" AND ", $sqlArray);

        $sql = "SELECT * FROM `$tableName` WHERE $conditions";
        
        return $this->setQuery($sql)->execute($params)->getQueryResults(PDO::FETCH_ASSOC);
    }

	/**
	 * Retrieve datas from db for a given id
	 * Returns an array of result
	 *
	 * @param string $tableName
	 * @param integer $id
	 * @return array
	 */
	public function getDatasById($tableName, $id)
	{
		return $this->getDatas($tableName, array("document_uid" => $id));
	}

	/**
	 * Retrieve datas from db for a given id and a given lang
	 * Returns an array of result
	 *
	 * @param string $tableName
	 * @param integer $id
	 * @param string $lang
	 * @return array
	 */
	public function getDatasByLangAndId($tableName, $id, $lang)
	{
        return $this->getDatas($tableName, array("document_uid" => $id, "document_lang" => $lang));
	}

	/**
	 * Retrieve localized datas for a given id
	 *
	 * @param string $tableName
	 * @param integer $id
	 * @param string $lang
	 * @return array
	 */
	public function getLocalizedDatasById($tableName, $id, $lang)
	{
		$i18nTable = $tableName."_i18n";

		$params = array();
		$params['id'] = $id;
		$params['idsec'] = $id;
		$params['lang'] = $lang;

		$sql = "SELECT * FROM `$tableName` base JOIN `$i18nTable` local WHERE base.document_uid=:id AND local.document_uid=:idsec AND local.document_lang=:lang";

		return $this->setQuery($sql)->execute($params)->getQueryResults(PDO::FETCH_ASSOC);
	}

	/**
	 * Retrieve revision datas from db
	 *
	 * @param string $tableName
	 * @param integer $id
	 * @param integer $revision
	 * @return f_document_baseDocument
	 */
	public function getRevisionDatasById($tableName, $id, $revision)
	{
        return $this->getDatas($tableName."_revisions", array("document_uid" => $id, "document_revision" => $revision));
	}
	
	/**
	 * Get the last revision for a given document id
	 *
	 * @param string $tableName
	 * @param integer $id
	 * @param string $lang
	 * @return integer
	 */
	public function getLastRevisionId($tableName, $id, $lang)
	{
		return $this->setQuery("SELECT `document_revision` FROM `$tableName` WHERE `document_uid`=:id AND `document_lang`=:lang")->execute(array("id" => $id, "lang" => $lang))->getQueryResult(PDO::FETCH_COLUMN);
	}

	/**
	 * Retrieve datas from db for a given id, a given revision number and a given lang
	 * Returns an array of result
	 *
	 * @param string $tableName
	 * @param integer $revision
	 * @param integer $id
	 * @param string $lang
	 * @return array
	 */
	public function getDatasByRevisionLangAndId($tableName, $revision, $id, $lang)
	{
        return $this->getDatas($tableName."_revisions_i18n", array("document_uid" => $id, "document_revision" => $revision, "document_lang" => $lang));
	}

	// ================================================= MISCELANEOUS

	/**
	 * Return the last inserted Id
	 *
	 * @return integer
	 */
	public function getLastInsertedId()
	{
		return $this->dbConnexion->lastInsertId();
	}

	/**
	 * Get next auto increment id
	 *
	 * @param string $tableName
	 * @return integer
	 */
	public function getNextTableId($tableName)
	{
		$status = $this->setQuery("SHOW TABLE STATUS LIKE :table")->execute(array('table' => $tableName))->getQueryResults(PDO::FETCH_ASSOC);

		return intval($status[0]['Auto_increment']);
	}

	/**
	 * Use at own risk, access to driver
	 *
	 * @return PDO
	 */
	public function getDriver()
	{
		return $this->dbConnexion;
	}

	/**
	 * Check if a table exist in database
	 *
	 * @param string $tableName
	 * @return boolean
	 */
	public function tableExists($tableName)
	{
		$query = $this->dbConnexion->query("SHOW TABLES LIKE '".$tableName."'")->fetchAll();
		return isset($query[0]);
	}
}